﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using Utils;

namespace QLSX.BO
{
    class SanPham
    {
        #region Property

        public int Stt { set; get; }
        public int SanPhamId { set; get; }
        public string TenSanPham { set; get; }
        public int SoLuong { set; get; }
        public int SoLuongThucTe { set; get; }
        public DateTime NgaySanXuat { set; get; }
        public DateTime NgayCapNhatSoLuong { set; get; }
        public double Duong { set; get; }
        public double Bot { set; get; }
        public double Nuoc { set; get; }

        #endregion

        public static List<SanPham> SelectAllSanPham()
        {
            List<SanPham> listSanPham = new List<SanPham>();
            int counter = 1;

            SqlConnection con = new SqlConnection(ApplicationConfig.GetConnectionString());
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SelectAllSanPham", con);
                cmd.CommandType = CommandType.StoredProcedure;                
                SqlDataReader dr = cmd.ExecuteReader();
                while (dr.Read())
                {
                    SanPham sp = new SanPham();
                    sp.Stt = counter++;
                    sp.SanPhamId = Int32.Parse(dr["SanPhamId"].ToString());
                    sp.TenSanPham = dr["TenSanPham"].ToString();
                    sp.SoLuong = Int32.Parse(dr["SoLuong"].ToString());
                    sp.SoLuongThucTe = Int32.Parse(dr["SoLuongThucTe"].ToString());
                    sp.NgaySanXuat = DateTime.Parse(dr["NgaySanXuat"].ToString());
                    sp.NgayCapNhatSoLuong = DateTime.Parse(dr["NgayCapNhatSoLuong"].ToString());
                    sp.Duong = Double.Parse(dr["Duong"].ToString());
                    sp.Bot = Double.Parse(dr["Bot"].ToString());
                    sp.Nuoc = Double.Parse(dr["Nuoc"].ToString());
                    listSanPham.Add(sp);
                }
                return listSanPham;
            }
            catch
            {
                return null;
            }
            finally
            {
                if (con.State == ConnectionState.Open)
                    con.Close();
            }
        }

        public static SanPham SelectSanPhamById(int sanPhamId)
        {
            SqlConnection con = new SqlConnection(ApplicationConfig.GetConnectionString());
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("SelectSanPhamBySanPhamId", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SanPhamid", sanPhamId);
                SqlDataReader dr = cmd.ExecuteReader();
                if (dr.Read())
                {
                    SanPham sp = new SanPham();
                    sp.TenSanPham = dr["TenSanPham"].ToString();
                    sp.SoLuong = Int32.Parse(dr["SoLuong"].ToString());
                    sp.SoLuongThucTe = Int32.Parse(dr["SoLuongThucTe"].ToString());
                    sp.NgaySanXuat = DateTime.Parse(dr["NgaySanXuat"].ToString());
                    sp.NgayCapNhatSoLuong = DateTime.Parse(dr["NgayCapNhatSoLuong"].ToString());
                    return sp;
                }
                else
                    return null;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                con.Close();
            }
        }

        public static bool AddSanPham(SanPham sp)
        {
            SqlConnection con = new SqlConnection(ApplicationConfig.GetConnectionString());
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("InsertSanPham", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@TenSanPham", sp.TenSanPham);
                cmd.Parameters.AddWithValue("@SoLuong", sp.SoLuong);
                cmd.Parameters.AddWithValue("@SoLuongThucTe", sp.SoLuongThucTe);
                cmd.Parameters.AddWithValue("@NgaySanXuat", sp.NgaySanXuat);
                cmd.Parameters.AddWithValue("@NgayCapNhatSoLuong", sp.NgayCapNhatSoLuong);
                cmd.Parameters.AddWithValue("@Duong", sp.Duong);
                cmd.Parameters.AddWithValue("@Bot", sp.Bot);
                cmd.Parameters.AddWithValue("@Nuoc", sp.Nuoc);
                // add parameters
                SqlParameter outId = new SqlParameter("@NewSanPhamId", SqlDbType.Int, int.MaxValue);
                outId.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(outId);
                cmd.ExecuteNonQuery();
                sp.SanPhamId = (int)cmd.Parameters["@NewSanPhamId"].Value;
                return true; // success 


            }
            catch (Exception ex)
            {
                return false;  // return error message
            }
            finally
            {
                con.Close();
            }
        }
        public static bool DeleteSanPham(int sanPhamId)
        {
            SqlConnection con = new SqlConnection(ApplicationConfig.GetConnectionString());
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("DeleteSanPham", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SanPhamId", sanPhamId);
                cmd.ExecuteNonQuery();
                return true; // success 
            }
            catch (Exception ex)
            {
                return false;  // return error message
            }
            finally
            {
                con.Close();
            }
        }
        public static bool UpdateSanPham(SanPham sp)
        {
            SqlConnection con = new SqlConnection(ApplicationConfig.GetConnectionString());
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("UpdateSanPham", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@SanPhamId", sp.SanPhamId);
                cmd.Parameters.AddWithValue("@TenSanPham", sp.TenSanPham);
                cmd.Parameters.AddWithValue("@SoLuong", sp.SoLuong);
                cmd.Parameters.AddWithValue("@SoLuongThucTe", sp.SoLuongThucTe);
                cmd.Parameters.AddWithValue("@NgaySanXuat", sp.NgaySanXuat);
                cmd.Parameters.AddWithValue("@NgayCapNhatSoLuong", sp.NgayCapNhatSoLuong);
                cmd.ExecuteNonQuery();
                return true; // success 
            }
            catch (Exception ex)
            {
                return false;  // return error message
            }
            finally
            {
                con.Close();
            }
        }
    }
}
